package benchmarks.tpch.mysql.queries;

public class Q21MySQL extends GenericQueryMySQL {
    public Q21MySQL() {
        super.queryName = queryName;
        super.query = query;
    }
    public static final String queryName = "Q21";
    public static final String query =
            "select\n" +
            "\ts_name,\n" +
            "\tcount(*) as numwait\n" +
            "from\n" +
            "\tsupplier,\n" +
            "\tlineitem l1,\n" +
            "\torders,\n" +
            "\tnation\n" +
            "where\n" +
            "\ts_suppkey = l1.l_suppkey\n" +
            "\tand o_orderkey = l1.l_orderkey\n" +
            "\tand o_orderstatus = 'F'\n" +
            "\tand l1.l_receiptdate > l1.l_commitdate\n" +
            "\tand exists (\n" +
            "\t\tselect\n" +
            "\t\t\t*\n" +
            "\t\tfrom\n" +
            "\t\t\tlineitem l2\n" +
            "\t\twhere\n" +
            "\t\t\tl2.l_orderkey = l1.l_orderkey\n" +
            "\t\t\tand l2.l_suppkey <> l1.l_suppkey\n" +
            "\t)\n" +
            "\tand not exists (\n" +
            "\t\tselect\n" +
            "\t\t\t*\n" +
            "\t\tfrom\n" +
            "\t\t\tlineitem l3\n" +
            "\t\twhere\n" +
            "\t\t\tl3.l_orderkey = l1.l_orderkey\n" +
            "\t\t\tand l3.l_suppkey <> l1.l_suppkey\n" +
            "\t\t\tand l3.l_receiptdate > l3.l_commitdate\n" +
            "\t)\n" +
            "\tand s_nationkey = n_nationkey\n" +
            "\tand n_name = 'SAUDI ARABIA'\n" +
            "group by\n" +
            "\ts_name\n" +
            "order by\n" +
            "\tnumwait desc,\n" +
            "\ts_name";
}
